package com.summer.data.dialect.oracle;

import com.summer.data.Jsql;
import com.summer.data.dialect.AbstractDialect;
import com.summer.data.exception.BaseException;
import com.summer.data.exception.SqlException;
import com.summer.data.jql.Context;
import com.summer.data.jql.concept.func.Nvl;
import com.summer.data.parameter.BlobParameter;
import com.summer.data.parameter.ClobParameter;
import com.summer.data.rs.extractor.KeysExtractor;
import com.summer.data.table.BaseTable;

import java.io.IOException;
import java.io.Writer;
import java.math.BigDecimal;
import java.sql.*;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

/**
 * @author 345480567@qq.com
 */
public class OracleDialect extends AbstractDialect {

    // https://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm#CNCPT313
    // Accessing and Manipulating Oracle Data
    // https://docs.oracle.com/en/database/oracle/oracle-database/23/jjdbc/accessing-and-manipulating-Oracle-data.html#GUID-1AF80C90-DFE6-4A3E-A407-52E805726778
    private static final Map<Class<?>, JDBCType> JAVA_2_JDBC = new HashMap<Class<?>, JDBCType>(){{
        put(Short.class, JDBCType.NUMERIC);
        put(Integer.class, JDBCType.NUMERIC);
        put(Long.class, JDBCType.NUMERIC);

        put(Float.class, JDBCType.NUMERIC);
        put(Double.class, JDBCType.NUMERIC);
        put(BigDecimal.class, JDBCType.DECIMAL);

        put(String.class, JDBCType.VARCHAR);

        put(Boolean.class, JDBCType.BOOLEAN);
        put(Byte.class, JDBCType.BIT);

        put(Date.class, JDBCType.TIMESTAMP);
        put(Timestamp.class, JDBCType.TIMESTAMP);

        put(ClobParameter.class, JDBCType.CLOB);
        put(BlobParameter.class, JDBCType.BLOB);

    }};

    //    "select * from (%s) where rownum between %d and %d "
    @Override
    public String getPageSql(String sql, Integer offset, Integer pageSize) {

        StringBuilder fmt = new StringBuilder();
        fmt.append("SELECT * FROM ( ");
        fmt.append(" SELECT TMP_PAGE.*, ROWNUM SD_ROW_ID FROM ( %s ) TMP_PAGE");
        fmt.append(") WHERE SD_ROW_ID > %d AND SD_ROW_ID <= %d");

        String pageSql = String.format(fmt.toString(), sql, offset, offset+pageSize);
        return pageSql;
    }


    @Override
    public Jsql getNvlJsql(Context ctx, Nvl nvlFunc) {
        if (nvlFunc.other == null) {
            return Jsql.format("NVL(%s, %s)",
                    nvlFunc.toFieldSql(ctx,this),
                    nvlFunc.result);
        } else {
            return Jsql.format("NVL(%s, %s, %s)",
                    nvlFunc.toFieldSql(ctx,this),
                    nvlFunc.result,
                    nvlFunc.other);
        }
    }

    @Override
    public Map<Class<?>, JDBCType> getJDBCTypeMap() {
        return JAVA_2_JDBC;
    }

    @Override
    public void setClob(PreparedStatement ps, int i, Object value) {

        try {
            Clob clob = ps.getConnection().createClob();
            try (Writer clobWriter = clob.setCharacterStream(1)) {

                if (value instanceof CharSequence) {
                    CharSequence charSequence = (CharSequence) value;
                    String string = charSequence.toString();
                    clobWriter.write(string);
                } else if (value instanceof ClobParameter) {
                    ClobParameter clobValue = (ClobParameter) value;
                    clobValue.write(clobWriter);
                } else {
                    throw new BaseException("不支持的参数类型: " + value.getClass().getName());
                }

            } catch (IOException e) {
                // todo:
                throw new RuntimeException(e);
            }

            ps.setClob(i, clob);
        } catch (SQLException e) {
            throw new SqlException(e);
        }
    }


    public String getSchemaSql() {
//        select * from dba_users;	-- 数据库里面所有用户，前提是你是有dba权限的帐号
//        select * from all_users;  -- 查看你能管理的所有用户；
//        select * from user_users; -- 查看当前用户信息
        return "select * from all_users";
    }

    public String getTableSql() {
        return "select table_name from user_tables";
    }

    public String getColumnSql(String tableName) {
        return "";
    }

    @Override
    public KeysExtractor buildKeysExtractor(BaseTable table) {
        // todo:
        return new KeysExtractor();
    }

    @Override
    public String getLikeSql() {
        return "'%'||?||'%'";
    }

    @Override
    public String getLeftLikeSql() {
        return "?||'%'";
    }

    @Override
    public String getRightLikeSql() {
        return "'%'||?";
    }

    @Override
    public String getFixedLikeSql(int leftFix, int rightFix) {
        StringBuffer buffer = new StringBuffer();
        // 左边:'____'
        if (leftFix > 0) {
            buffer.append("'");
        }
        for (int i = 0; i < leftFix; i++) {
            buffer.append("_");
        }
        if (leftFix > 0) {
            buffer.append("'||");
        }

        buffer.append("?");

        // 右边:'____'
        if (rightFix > 0) {
            buffer.append("||'");
        }
        for (int i = 0; i < rightFix; i++) {
            buffer.append("_");
        }
        if (rightFix > 0) {
            buffer.append("'");
        }

        return buffer.toString();
    }

}

